package com.huayue.dao;

import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Component;

import com.huayue.entity.DepotInOrder;
import com.huayue.util.PageHelper;
import com.mysql.DBHelper;

/**
 * 入库单
 * 
 */
@Component
public class DepotInOrderDao implements BaseDao<DepotInOrder> {
    /**
     * 添加方法
     *
     * @param entity
     * @return
     */
    @Override
    public int insert(DepotInOrder entity) {
        return DBHelper.executeUpdate(
            "insert into depot_in_order (ordernumber,employee_id_apply, purchase_order_id, sum, is_status) values (?,?,?,?,?);",
            entity.getOrdernumber(), entity.getEmployee_id_apply(), entity.getPurchaseOrderId(), entity.getSum(), 0);
    }

    /**
     * 修改方法
     *
     * @param entity
     * @return
     */
    @Override
    public int update(DepotInOrder entity) {
        return DBHelper.executeUpdate("update depot_in_order set sum=?, is_status=? where id=? ", entity.getSum(),
            entity.getIsStatus(), entity.getId());
    }

    /**
     * 删除方法
     *
     * @param entity
     * @return
     */
    @Override
    public int delete(DepotInOrder entity) {
        return DBHelper.executeUpdate("update depot_in_order set is_deleted=1 where id=? ", entity.getId());
    }

    /**
     * 查询方法 传入可变参数
     *
     * @param obj
     * @return
     */
    @Override
    public List<Map<String, Object>> findAll(Object... obj) {
        return DBHelper.executeQuery("select * from depot_in_order where is_deleted=0");
    }

    /**
     * 查询未入库的进货单
     *
     * @param page
     * @param obj
     * @return
     */
    @Override
    public List<Map<String, Object>> findPage(PageHelper page, Object... obj) {
        DepotInOrder depot = (DepotInOrder)obj[0];
        String sql =
            "select po.id,po.ordernumber,IFNULL(po.buycount-pg.diosum,po.buycount) buycount,po.is_status,po.gmt_create,po.remark,su.name suname,em.realname apply,emp.realname retify from purchase_order po \n"
                + "left join supplier su on po.supplier_id=su.id\n"
                + "left join employee em on po.employee_id_apply=em.id\n"
                + "left join employee emp on po.employee_id_retify=emp.id\n"
                + "left join (select sum(sum) diosum,dio.purchase_order_id from depot_in_order dio GROUP BY purchase_order_id) pg on pg.purchase_order_id=po.id\n"
                + "where po.is_status = 1 or po.is_status = 2";
        if (depot.getOrdernumber() != null && !"".equals(depot.getOrdernumber())) {
            sql += " and concat(emp.realname,po.ordernumber,su.name) like '%" + depot.getOrdernumber() + "%'";
        }
        return DBHelper.executeQuery(sql + " order by po.gmt_create desc  limit ?,?", page.getBegin(),
            page.getPageSize());
    }

    /**
     * 查询一条数据 通常对象中只需存有id
     *
     * @param entity
     * @return
     */
    @Override
    public Map<String, Object> findOne(DepotInOrder entity) {
        return DBHelper.executeQuery(
            "select dio.*,po.ordernumber pordernumber,dio.remark,sp.name spname from depot_in_order dio \n"
                + "left join purchase_order po on dio.purchase_order_id=po.id left join supplier sp on po.supplier_id=sp.id where dio.id=?",
            entity.getId()).get(0);
    }

    /**
     * 查询未入库的进货单总条数
     *
     * @param obj
     * @return
     */
    @Override
    public int count(Object... obj) {
        DepotInOrder depot = (DepotInOrder)obj[0];
        String sql = "select count(*) counts from purchase_order po \n"
            + "left join supplier su on po.supplier_id=su.id\n"
            + "left join employee em on po.employee_id_apply=em.id\n"
            + "left join employee emp on po.employee_id_retify=emp.id\n"
            + "left join (select sum(sum) diosum,dio.purchase_order_id from depot_in_order dio GROUP BY purchase_order_id) pg on pg.purchase_order_id=po.id\n"
            + "where po.is_status = 1 or po.is_status = 2";
        if (depot.getOrdernumber() != null && !"".equals(depot.getOrdernumber())) {
            sql += " and concat(emp.realname,po.ordernumber,su.name) like '%" + depot.getOrdernumber() + "%'";
        }
        sql += " order by po.gmt_create desc ";
        return Integer.valueOf(DBHelper.executeQuery(sql).get(0).get("counts").toString());
    }

    /**
     * 找最大的id
     * 
     * @return
     */
    public int findMaxId() {
        return Integer.valueOf(
            DBHelper.executeQuery("select IFNULL(max(id),0) maxid from depot_in_order").get(0).get("maxid").toString());

    }

    /**
     * 查询待入库的单个订货单
     * 
     * @param depot
     * @return
     */
    public Map<String, Object> findNotInDepotPurchase(DepotInOrder depot) {
        return DBHelper.executeQuery("select po.id,po.ordernumber,sp.name spname from purchase_order po "
            + "left join supplier sp on po.supplier_id=sp.id where po.id=?", depot.getId()).get(0);
    }

    /**
     * 查询待入库的单个订货单详情
     *
     * @param depot
     * @return
     */
    public List<Map<String, Object>> findNotInDepotPurchaseDetails(DepotInOrder depot) {
        return DBHelper.executeQuery(
            "select pod.product_id,p.name pname,p.product_number product_number,ifnull(pod.number-sumd.sumnumber,pod.number) number,p.unit unit,pod.cause remark from purchase_order_details pod "
                + "left join purchase_order po on po.id=pod.purchase_order_id "
                + "left join product p on pod.product_id=p.id "
                + "left join (select diod.product_id,sum(number) sumnumber from depot_in_order_details diod "
                + "left join depot_in_order dio on dio.id=diod.depot_in_order_id "
                + "left join purchase_order po on dio.purchase_order_id=po.id  where po.id=? "
                + "GROUP BY product_id) sumd on sumd.product_id=pod.product_id where po.id=? ",
            depot.getId(), depot.getId());
    }

    /**
     * 修改最新入库单的状态
     * 
     * @param depot
     * @return
     */
    public int updateIsStatus(DepotInOrder depot) {
        return DBHelper.executeUpdate(
            "update depot_in_order set is_status=? where id=(select MAX(id) from depot_in_order)", depot.getIsStatus());
    }

    /**
     * 查询已入库的订单
     *
     * @param page
     * @param obj
     * @return
     */
    public List<Map<String, Object>> findPageYes(PageHelper page, Object... obj) {
        DepotInOrder depotInOrder = (DepotInOrder)obj[0];
        String sql =
            "select dio.ordernumber,dio.id,po.ordernumber pordernumber,dio.sum buycount,dio.gmt_create,dio.remark,su.name suname,em.realname apply from depot_in_order dio\n"
                + "left join purchase_order po on dio.purchase_order_id=po.id\n"
                + "left join supplier su on po.supplier_id=su.id\n"
                + "left join employee em on dio.employee_id_apply=em.id\n"
                + "left join employee emp on po.employee_id_retify=emp.id where 1=1 ";
        if (depotInOrder.getOrdernumber() != null && !"".equals(depotInOrder.getOrdernumber())) {
            sql += " and concat(emp.realname,dio.ordernumber,po.ordernumber,su.name) like '%"
                + depotInOrder.getOrdernumber() + "%'";
        }
        sql += " and dio.is_status = 0 ";
        return DBHelper.executeQuery(sql + " order by dio.gmt_create desc limit ?,?;", page.getBegin(),
            page.getPageSize());
    }

    /**
     * 查询已入库的订单总数
     *
     * @param obj
     * @return
     */
    public int countYes(Object... obj) {
        DepotInOrder depotInOrder = (DepotInOrder)obj[0];
        String sql = "select count(*) counts from depot_in_order dio\n"
            + "left join purchase_order po on dio.purchase_order_id=po.id\n"
            + "left join supplier su on po.supplier_id=su.id\n"
            + "left join employee em on dio.employee_id_apply=em.id\n"
            + "left join employee emp on po.employee_id_retify=emp.id where 1=1 ";
        if (depotInOrder.getOrdernumber() != null && !"".equals(depotInOrder.getOrdernumber())) {
            sql += " and concat(emp.realname,dio.ordernumber,po.ordernumber,su.name) like '%"
                + depotInOrder.getOrdernumber() + "%'";
        }
        sql += " and dio.is_status = 0 order by dio.gmt_create desc ";
        return Integer.valueOf(DBHelper.executeQuery(sql).get(0).get("counts").toString());
    }

    /**
     * 查询已入库的订单详情
     * 
     * @param depot
     * @return
     */
    public List<Map<String, Object>> findYesInDepotPurchaseDetails(DepotInOrder depot) {
        String sql =
            "select diod.number,p.name pname,p.product_number,p.unit,dio.ordernumber,e.realname apply from depot_in_order_details diod\n"
                + "left join depot_in_order dio on diod.depot_in_order_id=dio.id\n"
                + "left join product p on diod.product_id=p.id\n"
                + "left join employee e on dio.employee_id_apply=e.id \n";
        if (depot.getId() != 0) {
            sql += "where dio.id=" + depot.getId();
        }
        return DBHelper.executeQuery(sql);
    }
}
